﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DBAccessLibrary.Model;
using Skynet.Data;
using Skynet.CommonLibrary.Extension;
using System.Data;
using System.Xml.Linq;
using Skynet.CommonLibrary.Extension.XML;
using System.Threading;
using System.Collections.ObjectModel;

namespace DBAccessLibrary
{
    public class DBFactory
    {
        //当前数据库链接
        public static ConnectionModel DBConnection { get; set; }
        //当前数据库的所有表信息
        public static List<DataBaseEntity> TableEntity { get; set; }
        public static List<DataBaseEntity> ProcEntity { get; set; }
        public static List<DataBaseEntity> ViewEntity { get; set; }
        //记录当前连接过的所有数据库
        public static ObservableCollection<DataBaseEntity> ServerList { get; set; }
        public static bool CreateDBEntity(ObservableCollection<DataBaseEntity> list, Action<string> log, Action<Action> begioninvoke)
        {
            if(DBConnection == null)
            {
                log("当前连接不可用。");
                return false;
            }
            if (!IsConnectDB(DBConnection))
            {
                log("当前数据库无法连接，请查看数据库是否正常。");
                return false;
            }
            ServerEntity server = new ServerEntity();
            server.Name = DBConnection.Server;
            server.ImageURL = "../Images/computers.png";

            DBEntity db = CreateDBEntity(server, DBConnection);
            DBEntity test = new DBEntity { Name = "正在连接，请等待……", ImageURL = "../Images/blue-loading.gif" };
            begioninvoke(() =>
            {
                DataBaseEntity first = list.FirstOrDefault(o => o.Name == server.Name);
                if (first != null)
                {
                    DataBaseEntity dbfirst = first.ChildNodes.FirstOrDefault(o => o.Name == db.Name);
                    if (dbfirst != null)
                    {
                        first.ChildNodes.Remove(dbfirst);
                    }
                    server = first as ServerEntity;
                }
                else
                {
                    list.Add(server);
                }

                server.ChildNodes.Add(test);
                server.IsExpanded = true;
            });

            DataSet tbds = null, columnds = null, viewds = null, procds = null,propramds=null, chds = null;
            ConnectDB(DBConnection,(connect,dataaccess) =>
            {
                string sql = string.Empty;
                log("获取数据库相关信息");
                try
                {
                    sql = string.Format(DBInfoConfig.GetInfo(connect.ConnectType, DBInfoType.AllTableInfo),connect.UserID.ToUpper());
                    //获取数据库中表的信息
                    tbds = dataaccess.ExecuteDataset(sql);
                }
                catch (Exception ex)
                { log("表信息获取失败，详细:"+ex.Message); }
                try
                {
                    sql = string.Format(DBInfoConfig.GetInfo(connect.ConnectType, DBInfoType.AllCloumnInfo), connect.UserID.ToUpper());
                    //获取所有表中的字段名
                    columnds = dataaccess.ExecuteDataset(sql);
                }
                catch (Exception ex)
                { log("字段信息获取失败，详细:" + ex.Message); }
                 try
                 {
                     sql = string.Format(DBInfoConfig.GetInfo(connect.ConnectType, DBInfoType.AllViewInfo), connect.UserID.ToUpper());

                     //获取所有视图
                     viewds = dataaccess.ExecuteDataset(sql);
                 }
                 catch (Exception ex)
                 { log("视图信息获取失败，详细:" + ex.Message); }
                 try
                 {
                     sql = string.Format(DBInfoConfig.GetInfo(connect.ConnectType, DBInfoType.AllProcInfo), connect.UserID.ToUpper());

                     //获取所有存储过程
                     procds = dataaccess.ExecuteDataset(sql);
                 }
                 catch (Exception ex)
                 { log("存储过程信息获取失败，详细:" + ex.Message); }
                 try
                 {
                     sql = string.Format(DBInfoConfig.GetInfo(connect.ConnectType, DBInfoType.ALLProcPramInfo), connect.UserID.ToUpper());
                     
                     //获取所有存储过程
                     propramds = dataaccess.ExecuteDataset(sql);
                 }
                 catch (Exception ex)
                 { log("存储过程参数信息获取失败，详细:" + ex.Message); }
                 try
                 {
                     //获取中文对照信息
                     chds = dataaccess.ExecuteDataset(DBInfoConfig.GetInfo(connect.ConnectType, DBInfoType.AllCHTableInfo));
                 }
                 catch (Exception ex)
                 { log("中文对照信息获取失败，详细:" + ex.Message); }
            });
            log("构建实体集合");
            CreateTableEntity(db, tbds, columnds, chds);
            CreateViewEntity(db, columnds, viewds);
            CreateProcEntity(db, procds, propramds);
            begioninvoke(() =>
            {
                server.ChildNodes.Remove(test);
                server.ChildNodes.Add(db);
                //改变状态
                foreach (DataBaseEntity serverEntity in list)
                {
                    foreach (DataBaseEntity dbEntity in serverEntity.ChildNodes.Where(o => o.Name != db.Name))
                    {
                        (dbEntity as DBEntity).Status = DBConnectStatus.OFF;
                    }
                }
                db.PatentNode = server;
                db.Status = DBConnectStatus.Connected;
            });
            log("数据库加载成功");
            return true;
        }

        //创建数据库实体
        private static DBEntity CreateDBEntity(DataBaseEntity parent, ConnectionModel connect)
        {
            DBEntity db = new DBEntity();
            db.Name = connect.DataBase;
            db.PatentNode = parent;
            db.ConnectionSource = connect;
            db.Status = DBConnectStatus.Connected;
            return db;
        }

        //创建表和列实体
        private static void CreateTableEntity(DBEntity parent, DataSet tbds, DataSet columnds, DataSet chds)
        {
            DescriptionEntity table = new DescriptionEntity();
            table.PatentNode = parent;
            table.Name = "表";
            if (tbds != null && tbds.Tables.Count > 0)
            {
                var columnList = columnds.Tables[0].AsEnumerable();
                foreach (DataRow tbrow in tbds.Tables[0].Rows)
                {
                    TableEntity tbEntity = new TableEntity();
                    tbEntity.Name = tbrow["TABLENAME"].ToString().Trim();
                    tbEntity.CHName = GetCHName(tbEntity.Name, null, chds);
                    tbEntity.PatentNode = table;

                   
                    //获取该表中的所有列
                    foreach (DataRow columnrow in columnList.Where(o => o.Field<string>("TABLENAME") == tbEntity.Name).OrderByDescending(o => Convert.ToInt32(o.Field<object>("PRIMARYKEY"))))
                    {
                        FieldEntity field = new FieldEntity();
                        field.Name = columnrow["COLUMNNAME"].ToString().Trim();
                        field.CHName = GetCHName(tbEntity.Name, field.Name, chds);
                        field.TypeName = columnrow["TYPENAME"].ToString().Trim();
                        field.Scale = columnrow["DATA_SCALE"].ToString();
                        field.Length = columnrow["LENGTH"].ToString().Trim();
                       
                        field.IsNull = columnrow["ISNULL"].ToString().Trim();
                        field.PatentNode = tbEntity;
                        
                        if (columnrow["PRIMARYKEY"].ToString() == string.Empty || columnrow["PRIMARYKEY"].ToString() == "0")
                        {
                            field.IsPrimaryKey = false;
                        }
                        else
                        {
                            field.IsPrimaryKey = true;
                        }
                        tbEntity.ChildNodes.Add(field);
                    }
                    table.ChildNodes.Add(tbEntity);
                }
            }
    
            parent.ChildNodes.Add(table);
        }
        //创建视图实体
        private static void CreateViewEntity(DBEntity parent, DataSet columnds, DataSet viewds)
        {
            DescriptionEntity view = new DescriptionEntity();
            view.Name = "视图";
            view.PatentNode = parent;
            if (viewds != null && viewds.Tables.Count > 0)
            {
                var columnList = columnds.Tables[0].AsEnumerable();
                foreach (DataRow row in viewds.Tables[0].Rows)
                {
                    string name = row["VIEWNAME"].ToString();
                    string text = row["CONTENT"].ToString();

                    if (DBConnection.ConnectType == DBType.Oracle)
                    {
                        ProcEntity entity = view.ChildNodes.FirstOrDefault(o => o.Name == name) as ProcEntity;
                        if (entity != null)
                        {
                            entity.Content += text;
                            continue;
                        }
                    }

                    ViewEntity viewEntity = new ViewEntity();
                    viewEntity.Name = name;
                    viewEntity.Content = text;
                    viewEntity.PatentNode = view;
                    //获取该表中的所有列
                    foreach (DataRow columnrow in columnList.Where(o => o.Field<string>("TABLENAME") == viewEntity.Name))
                    {
                        FieldEntity field = new FieldEntity();
                        field.Name = columnrow["COLUMNNAME"].ToString();

                        field.TypeName = columnrow["TYPENAME"].ToString();
                        if (field.TypeName == "VARCHAP")
                        { field.TypeName = "VARCHAR"; }
                        field.Length = columnrow["LENGTH"].ToString();
                        field.Scale = columnrow["DATA_SCALE"].ToString();
                        field.IsPrimaryKey = string.IsNullOrEmpty(columnrow["PRIMARYKEY"].ToString()) ? false : true;
                        field.IsNull = columnrow["ISNULL"].ToString();
                        field.PatentNode = viewEntity;
                        viewEntity.ChildNodes.Add(field);
                    }
                    view.ChildNodes.Add(viewEntity);
                }
            }
            parent.ChildNodes.Add(view);
        }
        //创建储存过程实体
        private static void CreateProcEntity(DBEntity parent, DataSet procds, DataSet propramds)
        {
            DescriptionEntity proc = new DescriptionEntity();
            proc.Name = "存储过程";
            proc.PatentNode = parent;
            if (procds != null && procds.Tables.Count > 0)
            {

                EnumerableRowCollection<DataRow> paramList = null;
                if (propramds != null)
                {
                    paramList = propramds.Tables[0].AsEnumerable();
                }
                foreach (DataRow row in procds.Tables[0].Rows)
                {
                    string name = row["PROCNAME"].ToString();
                    string text = row["CONTENT"].ToString();
                   
                    
                        ProcEntity entity = proc.ChildNodes.FirstOrDefault(o => o.Name == name) as ProcEntity;
                        if (entity != null)
                        {
                            entity.Content += text;
                            continue;
                        }
                    

                    ProcEntity procEntity = new ProcEntity();
                    procEntity.Name = name;
                    procEntity.Content = text;
                    procEntity.PatentNode = proc;

                    if (paramList != null)
                    {
                        foreach (DataRow prow in paramList.Where(o => o.Field<string>("PROCNAME") == name))
                        {
                            ProcParamEntity paramEntity = new ProcParamEntity() {
                                Name = prow["PARMNAME"].ToString(),
                                Length = prow["LENGTH"].ToString(),
                                TypeName = prow["TYPENAME"].ToString(),
                                Status = prow["STATUS"].ToString(),
                                Index = Convert.ToInt32(prow["INDEX"].ToString()),
                                Scale = prow["SCALE"].ToString()
                            };
                            procEntity.ChildNodes.Add(paramEntity);
                        }
                    }


                    proc.ChildNodes.Add(procEntity);
                }
            }
            parent.ChildNodes.Add(proc);
        }
        //构建连接字符串
        private static DatabaseType GetConnectStr(ConnectionModel connect, out string connectSTR)
        {
            if (connect.ConnectType == DBType.Oracle)
            {
                connectSTR = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={3})(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME={0})));User Id={1};Password={2};Pooling=true;Min Pool Size=0;Max Pool Size=100;Connection Timeout=60;Connection Lifetime=0;Persist Security Info=true ";
            }
            else
            {
                connectSTR = "Database={0};user id={1};password={2};server={3}";
            }
            connectSTR = string.Format(connectSTR,
                connect.DataBase,
                connect.UserID,
                connect.PassWord,
                connect.Server);
            switch (connect.ConnectType)
            {
                case DBType.DB2:
                    return DatabaseType.DB2;
                case DBType.SQLServer:
                    return DatabaseType.MSSQLServer;
                case DBType.Oracle:
                    return DatabaseType.Oracle;
            }
            return DatabaseType.DB2;
        }
        //获取中文名称
        private static string GetCHName(string tablename, string columnname, DataSet ds)
        {
            DataRow row = null;
            if (ds == null || ds.Tables.Count == 0)
            { return string.Empty; }

            if (string.IsNullOrEmpty(columnname))
            {
                row = ds.Tables[0].AsEnumerable().FirstOrDefault(o => o.Field<string>("TABLENAME") == tablename);
                if (row != null)
                    return row["CHINATABLENAME"].ToString().Trim();
            }
            else
            {
                row = ds.Tables[0].AsEnumerable().FirstOrDefault(o =>o.Field<string>("TABLENAME") == tablename && o.Field<string>("COLUMNNAME") == columnname);
                if (row != null)
                    return row["CHINACOLUMNNAME"].ToString().Trim();
            }
            return string.Empty;
        }

        private static IDbTransaction _transaction = null;
        public static void RollbackTransaction(ConnectionModel connect)
        {
            if (_transaction != null)
            {
                _transaction.Rollback();
                _transaction = null;
            }
            IDataAccess dataaccess = HttpRuntimeCache.GetCache<IDataAccess>(connect.GetHashCode().ToString());
            if (dataaccess != null)
            {
                dataaccess.Close();
                HttpRuntimeCache.RemoveCache(connect.GetHashCode().ToString());
            }
        }
        public static void CommitTransaction(ConnectionModel connect)
        {
             if (_transaction != null)
             {
                 _transaction.Commit();
                 _transaction = null;
             }
             IDataAccess dataaccess = HttpRuntimeCache.GetCache<IDataAccess>(connect.GetHashCode().ToString());
             if (dataaccess != null)
             {
                 dataaccess.Close();
                 HttpRuntimeCache.RemoveCache(connect.GetHashCode().ToString());
             }
        }
        /// <summary>
        /// 连接数据库
        /// </summary>
        /// <param name="connect"></param>
        public static void ConnectDB(ConnectionModel connect, Action<ConnectionModel,IDataAccess> exec,bool isTransaction =false)
        {
            try
            {
                if (connect == null)
                {
                    throw new Exception("当前链接已断开，请重新连接.");
                }
                IDataAccess dataaccess = null;
                if (isTransaction)
                {
                    dataaccess = HttpRuntimeCache.GetCache<IDataAccess>(connect.GetHashCode().ToString());
                }
                if (dataaccess == null)
                {
                    //生成连接字符串
                    string connectStr = string.Empty;
                    PersistenceProperty.DatabaseType = GetConnectStr(connect, out connectStr);
                    PersistenceProperty.ConnectionString = connectStr;
                    dataaccess = DataAccessFactory.instance.CreateDataAccess();
                    if (isTransaction)
                    {
                        HttpRuntimeCache.SetCache(connect.GetHashCode().ToString(), dataaccess, 5);
                    }
                }
               
                
                dataaccess.Open();
                
                if (isTransaction && _transaction == null)
                {
                    _transaction = dataaccess.BeginTransaction();
                }
                try
                {
                    if (exec != null)
                    {
                        exec(connect, dataaccess);
                    }
                }
                catch
                {
                    throw;
                }
                finally
                {
                    if (!isTransaction)
                    {
                        dataaccess.Close();
                    }
                }
            }
            catch
            {
                throw;
            }
        }
        public static void ConnectDB(ConnectionModel connect)
        {
            try
            {
                if (connect == null)
                {
                    throw new Exception("当前链接无效，请重新配置.");
                }
                //生成连接字符串
                string connectStr = string.Empty;
                PersistenceProperty.DatabaseType = GetConnectStr(connect, out connectStr);
                PersistenceProperty.ConnectionString = connectStr;
                IDataAccess dataaccess = DataAccessFactory.instance.CreateDataAccess();
                dataaccess.Open();
                dataaccess.Close();
                DBConnection = connect;
            }
            catch
            {
                throw;
            }
        }
        public static void ExecSql(string sql, ConnectionModel connect = null, List<DBParam> paramlist = null, bool isTrans = false)
        {
            if (connect == null)
                connect = DBConnection;
            ConnectDB(connect, (c, i) => {
                if (paramlist != null && paramlist.Count > 0)
                {
                    QueryParameterCollection qpc = new QueryParameterCollection();
                    paramlist.ForEach(o =>
                    {
                        if (string.IsNullOrEmpty(o.TypeName))
                        {
                            qpc.Add("@" +o.Name, o.Value);
                        }
                        else
                        {
                            qpc.Add(new QueryParameter("@" + o.Name, o.Value, o.TypeName, ParameterDirection.Input));
                        }
                    });
                    i.ExecuteNonQuery(sql, qpc);
                }
                else
                {
                    i.ExecuteNonQuery(sql);
                }
            },isTrans);
        }
        public static object ExecScalar(string sql, ConnectionModel connect = null, List<DBParam> paramlist = null, bool isTrans = false)
        {
            if (connect == null)
                connect = DBConnection;
            object result = null;
            ConnectDB(connect, (c, i) =>
            {
                if (paramlist != null && paramlist.Count > 0)
                {
                    QueryParameterCollection qpc = new QueryParameterCollection();
                    paramlist.ForEach(o =>
                    {
                        if (string.IsNullOrEmpty(o.TypeName))
                        {
                            qpc.Add(o.Name, o.Value);
                        }
                        else
                        {
                            qpc.Add(new QueryParameter(o.Name, o.Value, o.TypeName, ParameterDirection.Input));
                        }
                    });
                   result = i.ExecuteScalar(sql, qpc);
                }
                else
                {
                   result= i.ExecuteScalar(sql);
                }
            },isTrans);
            return result;
        }
        public static DataSet ExecSqlToDS(string sql, ConnectionModel connect = null, List<DBParam> paramlist = null, bool isTrans = false)
        {
            DataSet ds = null;
            if (connect == null)
                connect = DBConnection;
            ConnectDB(connect, (c, i) =>
            {
                ds = new DataSet();
                if (paramlist != null && paramlist.Count > 0)
                {
                    QueryParameterCollection qpc = new QueryParameterCollection();
                    paramlist.ForEach(o =>
                    {
                        if (string.IsNullOrEmpty(o.TypeName))
                        {
                            qpc.Add(o.Name, o.Value);
                        }
                        else
                        {
                            qpc.Add(new QueryParameter(o.Name, o.Value, o.TypeName, ParameterDirection.Input));
                        }
                    });
                    i.ExecuteDataset(sql, qpc,ds);
                }
                else
                {
                    i.ExecuteDataset(sql,ds);
                }
            },isTrans);
            return ds;
        }

        /// <summary>
        /// 检查连接是否正常
        /// </summary>
        /// <param name="connect"></param>
        /// <returns>true:正常  false:出错</returns>
        public static bool IsConnectDB(ConnectionModel connect)
        {
            try
            {
                if (connect == null)
                {
                    return false;
                }
                //生成连接字符串
                string connectStr = string.Empty;
                PersistenceProperty.DatabaseType = GetConnectStr(connect, out connectStr);
                PersistenceProperty.ConnectionString = connectStr;
                IDataAccess dataaccess = DataAccessFactory.instance.CreateDataAccess();
                dataaccess.Open();
                dataaccess.Close();
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 获取表和列的信息
        /// </summary>
        /// <param name="connection"></param>
        public static List<TableEntity> GetTableAndColumnInfos(ConnectionModel connection)
        {
            if (connection == null)
            {
                return null;
            }
            DataSet tbds = null,columnds = null;
            ConnectDB(connection,(connect,dataaccess) =>
           {
               try
               {
                   //获取数据库中表的信息
                   tbds = dataaccess.ExecuteDataset(DBInfoConfig.GetInfo(connect.ConnectType, DBInfoType.AllTableInfo));
               }
               catch 
               {
                   return;
               }
               try
               {
                   //获取所有表中的字段名
                   columnds = dataaccess.ExecuteDataset(DBInfoConfig.GetInfo(connect.ConnectType, DBInfoType.AllCloumnInfo));
               }
               catch
               { return; }
            
           });

            //添加数据
            if (tbds != null && tbds.Tables.Count > 0)
            {
                List<TableEntity> list = new List<TableEntity>();
                var columnList = columnds.Tables[0].AsEnumerable();
                foreach (DataRow tbrow in tbds.Tables[0].Rows)
                {
                    TableEntity tbEntity = new TableEntity();
                    tbEntity.Name = tbrow["TABLENAME"].ToString().Trim();
                   
                    //获取该表中的所有列
                    foreach (DataRow columnrow in columnList.Where(o => o.Field<string>("TABLENAME") == tbEntity.Name).OrderByDescending(o => Convert.ToInt32(o.Field<object>("PRIMARYKEY"))))
                    {
                        FieldEntity field = new FieldEntity();
                        field.Name = columnrow["COLUMNNAME"].ToString().Trim();
                        field.TypeName = columnrow["TYPENAME"].ToString().Trim();
                       
                        if (field.TypeName == "VARCHAP")
                        { field.TypeName = "VARCHAR"; }
                        field.Length = columnrow["LENGTH"].ToString().Trim();
                        field.Scale = columnrow["DATA_SCALE"].ToString();
                        field.IsNull = columnrow["ISNULL"].ToString().Trim();
                        field.PatentNode = tbEntity;

                        if (columnrow["PRIMARYKEY"].ToString() == string.Empty || columnrow["PRIMARYKEY"].ToString() == "0")
                        {
                            field.IsPrimaryKey = false;
                        }
                        else
                        {
                            field.IsPrimaryKey = true;
                        }
                        tbEntity.ChildNodes.Add(field);
                    }
                    list.Add(tbEntity);
                }
                return list;
            }
            return null;
        }

        /// <summary>
        /// 添加 中文对照表
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="fieldName"></param>
        /// <param name="chtablename"></param>
        /// <param name="chfieldname"></param>
        /// <param name="fieldType"></param>
        /// <param name="fieldLength"></param>
        /// <param name="isPrimary"></param>
        public static void AddDescription(string tableName, string fieldName,string chtablename,string chfieldname,string fieldType,string fieldLength,bool isPrimary)
        {
            string sql = DBInfoConfig.GetInfo(DBConnection.ConnectType, DBInfoType.IsDescription);
            DataSet ds = ExecSqlToDS(sql);
            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count == 0)
                {
                    //创建表
                    sql = @"create table T_FIELD_DICTIONARY (
                           TABLENAME                        VARCHAR(50)        Not Null,        --表名
                           FIELDNAME                        VARCHAR(30)        Not Null,        --字段名
                           CHINESENAME                      VARCHAR(50)            Null,        --中文名
                           CHINESETABLENAME                 VARCHAR(40)            Null,        --中文表名
                           COMMENTARY                       VARCHAR(100)           Null,        --备注
                           COMPUTEFIELD                     VARCHAR(4)             Null,        --计算字段
                           COMPUTEFORMULA                   VARCHAR(60)            Null,        --计算公式
                           DICTIONARYID                     VARCHAR(10)            Null,        --字典ID
                           FIELDLENGTH                      INTEGER                Null,        --字段长度
                           FIELDPRECISION                   INTEGER                Null,        --字段精度
                           FIELDTYPE                        VARCHAR(20)            Null,        --字段类型
                           ISFORERGNKEY                     VARCHAR(2)             Null,        --外键
                           ISPRIMARY                        VARCHAR(2)             Null,        --主键
                           SHOW                             VARCHAR(4)             Null,        --显示
                           SHOWORDER                        INTEGER                Null,        --显示顺序
                           SHOWWIDTH                        INTEGER                Null,        --显示宽度
                           primary key  (TABLENAME,FIELDNAME)
                        )";
                    ExecSql(sql);
                }
            }
            //
            sql =string.Format( "select TABLENAME from T_FIELD_DICTIONARY where TABLENAME='{0}' and FIELDNAME ='{1}'",tableName,fieldName);
            ds = ExecSqlToDS(sql);
            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    //更新
                    sql = string.Format("update T_FIELD_DICTIONARY set CHINESETABLENAME='{1}',CHINESENAME='{3}',FIELDTYPE='{4}',FIELDLENGTH={5},ISPRIMARY='{6}' where TABLENAME='{0}' and FIELDNAME ='{2}' ",
                        tableName, chtablename, fieldName, chfieldname, fieldType, fieldLength,isPrimary ? "1" : "0");
                }
                else
                {
                    //插入
                    sql = string.Format("insert into T_FIELD_DICTIONARY(TABLENAME,CHINESETABLENAME,FIELDNAME,CHINESENAME,FIELDTYPE,FIELDLENGTH,ISPRIMARY) values('{0}','{1}','{2}','{3}','{4}',{5},'{6}')",
                        tableName, chtablename, fieldName, chfieldname, fieldType, fieldLength, isPrimary ? "1" : "0");
                }
                ExecSql(sql);
            }
        }

        /// <summary>
        /// 添加 Tab 后的快捷信息
        /// </summary>
        /// <param name="text"></param>
        /// <returns></returns>
        public static string CreateTabText(string text)
        {
            switch (text.ToUpper().Trim())
            {
                case "S":
                    return "SELECT * FROM";
                case "U":
                    return "UPDATE";
                case "D":
                    return "DELETE FROM";
                case "AC":
                    return string.Format(SqlCommandFormat(DBCommandType.AddColumn), "{TabName}", "{ColumnName}", "{TypeName}", "{Length}");
                case "EC":
                    return string.Format(SqlCommandFormat(DBCommandType.EditColumn), "{TabName}", "{ColumnName}", "{TypeName}", "{Length}");
                case "EN":
                    return string.Format(SqlCommandFormat(DBCommandType.EditIsNull), "{TabName}", "{ColumnName}", "{NULL|NOT NULL}");
                case "EI":
                    return string.Format(SqlCommandFormat(DBCommandType.ErrorInfo), "{TBSPACEID}", "{TABLEID}", "{COLNO}");
                case "DT":
                    return DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                default:
                    return string.Empty;
            }
        }

        /// <summary>
        /// 获取 基本SQL命令
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public static string SqlCommandFormat(DBCommandType type, ConnectionModel connect = null)
        {
            if (connect == null)
                connect = DBConnection;
            return DBInfoConfig.GetCommandInfo(connect.ConnectType, type);
        }

        /// <summary>
        /// 根据数据库错误信息，返回详细内容
        /// </summary>
        /// <param name="errorMsg"></param>
        /// <returns></returns>
        public static string GetErrorInfo(string errorMsg)
        {
            if (DBConnection.ConnectType == DBType.DB2)
            {
                try
                {
                    if (errorMsg.Contains("TBSPACEID=") &&
                          errorMsg.Contains("TABLEID=") &&
                          errorMsg.Contains("COLNO="))
                    {
                        int start = errorMsg.IndexOf("TBSPACEID=") + "TBSPACEID=".Length;
                        string TBSPACEID = errorMsg.Substring(start, errorMsg.IndexOf(',', start) - start);
                        start = errorMsg.IndexOf("TABLEID=") + "TABLEID=".Length;
                        string TABLEID = errorMsg.Substring(start, errorMsg.IndexOf(',', start) - start);
                        start = errorMsg.IndexOf("COLNO=") + "COLNO=".Length;
                        string COLNO = errorMsg.Substring(start, errorMsg.IndexOf('"', start) - start);

                        string sql = string.Format(DBInfoConfig.GetCommandInfo(DBType.DB2, DBCommandType.ErrorInfo),
                            TBSPACEID, TABLEID, COLNO);
                        return "列" + ExecScalar(sql).ToString() + " 参数不正确……";
                    }
                }
                catch { }
            }
            return string.Empty;
        }

        /// <summary>
        /// 返回存储过程 参数信息
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public static string GetProcParamInfo(ProcEntity entity)
        {
            if (entity == null)
                return string.Empty;

            if (entity.ChildNodes.Count > 0)
            {
                List<string> list = new List<string>();
                foreach (ProcParamEntity param in entity.ChildNodes)
                {
                    if (param.Status == "IN")
                    {
                        list.Add(string.Format("{0}[{1}({2})]", Environment.NewLine, param.Name, param.TypeName));
                    }
                    else
                    {
                        if (DBFactory.DBConnection.ConnectType == DBType.DB2)
                        {
                            list.Add(Environment.NewLine + "?");
                        }
                        else
                        {
                            list.Add(string.Format("{0}[{1}({2})-OUT]", Environment.NewLine, param.Name, param.TypeName));
                        }
                    }
                }
                return "(" + string.Join(",", list.ToArray()) + ")";
            }
            else
            {
                return "()";
            }
        }
    }
}
